package com.lbs.droiddoctor.database;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.management.Query;

import sun.font.EAttribute;

import com.lbs.droiddoctor.Disease;
import com.lbs.droiddoctor.data.DiseaseData;
import com.lbs.droiddoctor.data.Symptom;

public class DbManager {
	private static DbManager db;
	private String sqlServer;
	private int port = 3306;
	// private String dbUser = "root";
	// private String dbPassword = "root";
	private String dbUser = "droiddoctor";
	private String dbPassword = "droiddoctor";
	private String dbName = "droiddoctor";
	private String dbServer = "ec2-50-19-213-178.compute-1.amazonaws.com";
	//private String dbServer = "localhost";

	public static synchronized DbManager getInstance() {
		if (db == null) {
			db = new DbManager();
		}

		return db;
	}

	private DbManager() {
		/*
		 * InetAddress address = null; try { address =
		 * InetAddress.getLocalHost(); } catch (UnknownHostException e) { //
		 * TODO Auto-generated catch block e.printStackTrace(); }
		 * 
		 * sqlServer = address.getHostAddress();
		 */
		// sqlServer = "localhost";
		sqlServer = dbServer;

		System.out.println("Server address  = " + sqlServer);
	}

	public String getPassword(String username) {
		String password = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "select password from user where username=\""
					+ username + "\"";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {
				password = result.getString("password");

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return password;
	}

	public boolean register(String name, String username, String password,
			String phone) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "insert into user values(\"" + name + "\",\""
					+ username + "\",\"" + password + "\",\"" + phone + "\")";
			System.out.println("query=" + query);

			statement.execute(query);
			statement.close();
			con.close();
			return true;

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public List<DiseaseData> getdisease() {
		List<DiseaseData> disList = new ArrayList<DiseaseData>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String query = "SELECT d.idDisease,DiseaseName,SymptomName FROM symptom s,disease d,dis_sym m where s.idSymptom=m.idSymptom and d.idDisease=m.idDisease";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);
			DiseaseData dis = new DiseaseData();
			int prevdiseaseId = -1;
			while (result.next()) {

				String diseaseName = result.getString("DiseaseName");
				String sym = result.getString("SymptomName");
				;

				int diseaseId = result.getInt("idDisease");
				if (prevdiseaseId == -1) {
					prevdiseaseId = diseaseId;
				}
				if (prevdiseaseId == diseaseId) {
					dis.setDiseaseName(diseaseName);
					dis.setSymptoms(sym);
				} else {
					disList.add(dis);
					dis = new DiseaseData();
					dis.setDiseaseName(diseaseName);
					System.out.print(diseaseName);
					dis.setSymptoms(sym);
				}

				prevdiseaseId = diseaseId;
			}
			disList.add(dis);
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return disList;

	}

	public List<DiseaseData> getdisease(String searchkey) {
		List<DiseaseData> disList = new ArrayList<DiseaseData>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String query = "SELECT d.idDisease,DiseaseName,SymptomName FROM symptom s,disease d,dis_sym m where s.idSymptom=m.idSymptom and d.idDisease=m.idDisease"
					+ " and d.DiseaseName like'%" + searchkey + "%'";

			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);
			DiseaseData dis = new DiseaseData();
			int prevdiseaseId = -1;
			while (result.next()) {

				String diseaseName = result.getString("DiseaseName");
				String sym = result.getString("SymptomName");
				;

				int diseaseId = result.getInt("idDisease");
				if (prevdiseaseId == -1) {
					prevdiseaseId = diseaseId;
				}
				if (prevdiseaseId == diseaseId) {
					dis.setDiseaseName(diseaseName);
					dis.setSymptoms(sym);
				} else {
					disList.add(dis);
					dis = new DiseaseData();
					dis.setDiseaseName(diseaseName);
					dis.setSymptoms(sym);
				}

				prevdiseaseId = diseaseId;

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return disList;

	}

	public ArrayList<Symptom> predictSymptom(String symptom1, String symptom2,
			String symptom3) {

		ArrayList<Symptom> list = new ArrayList<>();

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String symString = "'" + symptom1 + "'";
			if (symptom2 != null) {
				symString += ",'" + symptom2 + "'";
			}
			if (symptom3 != null) {
				symString += ",'" + symptom3 + "'";
			}
			/*
			 * String query =
			 * "select SymptomName from symptom where idSymptom in(select idSymptom from dis_sym"
			 * + " where idDisease in(select idDisease from " +
			 * "disease where idDisease in(select idDisease from dis_sym " +
			 * "where idSymptom in(select idSymptom from symptom " +
			 * "where SymptomName='"
			 * +symString+"'))))and(idSymptom not in(select idSymptom" +
			 * " from symptom where SymptomName='"+symString+"'))";
			 */
			String query = "select SymptomName from symptom s where idSymptom in"
					+ "(select idSymptom from dis_sym where idDisease in("
					+ "select idDisease from disease where idDisease in"
					+ "(select idDisease from dis_sym  where idSymptom in("
					+ "select idSymptom from symptom where SymptomName in("
					+ symString
					+ ")))))"
					+ "and(s.SymptomName "
					+ "not in("
					+ symString + "))";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			int prevdiseaseId = -1;
			while (result.next()) {

				String symptomName = result.getString("SymptomName");

				Symptom syptom = new Symptom();
				syptom.setSymptomName(symptomName);
				list.add(syptom);

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	public ArrayList<DiseaseData> predictDisease(String[] symptom) {
		ArrayList<DiseaseData> list = new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();

			String symString = "'" + symptom[0] + "'";

			for (int i = 1; i < symptom.length; i++) {
				if (i == symptom.length - 1) {
					symString += ",'" + symptom[i] + "'";
				} else {
					symString += ",'" + symptom[i] + "',";
				}
			}

			/*
			 * select DiseaseName from disease where idDisease in(select
			 * idDisease from symptom s,dis_sym o where
			 * (s.idSymptom=o.idSymptom)and(s.SymptomName='pain chest'))and
			 * idDisease in(select idDisease from symptom s,dis_sym o where
			 * (s.idSymptom=o.idSymptom)and(s.SymptomName='pain'))and idDisease
			 * in(select idDisease from symptom s,dis_sym o where
			 * (s.idSymptom=o.idSymptom)and(s.SymptomName='night sweat'))
			 */

			String query = "select DiseaseName from disease where idDisease in("
					+ "select idDisease from dis_sym  where idSymptom in("
					+ "select idSymptom from symptom where SymptomName in("
					+ symString + ")))";
			
			String que = "select DiseaseName from disease where idDisease in " +
					"(select idDisease from symptom s,dis_sym o where ";
			
			String condition = "idDisease in(select idDisease from symptom s,dis_sym o where" +
					" (s.idSymptom=o.idSymptom)and(s.SymptomName='$'))";
			
			for (int i=0;i<symptom.length;i++) {
				if(i==0) {
					String cond = condition.replace("$", symptom[i]);
					que+=cond+")";
				}else {
					String cond = condition.replace("$", symptom[i]);
					que+=" and "+cond;

				}
			}
			
			System.out.println("query=" + que);
			ResultSet result = statement.executeQuery(que);

			int prevdiseaseId = -1;
			while (result.next()) {

				String diseaseName = result.getString("DiseaseName");

				DiseaseData disease = new DiseaseData();
				disease.setDiseaseName(diseaseName);
				list.add(disease);

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public int getDiseaseId(String disease) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String query = "select idDisease from disease where DiseaseName='"
					+ disease + "'";

			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {

				int diseaseId = result.getInt("idDisease");
				return diseaseId;

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return -1;
	}

	public int getSymptomId(String symptom) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String query = "select idSymptom from symptom where SymptomName='"
					+ symptom + "'";

			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {

				int symptomId = result.getInt("idSymptom");
				return symptomId;

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return -1;
	}

	public void addDisease(String disease, String[] symArray) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement= con.createStatement();

			String insertDiseaseQuery = "replace into disease(diseaseName)values('"
					+ disease + "')";
			System.out.print("insert Query: " + insertDiseaseQuery);
			statement.execute(insertDiseaseQuery);
			statement.close();
			con.close();

			int diseaseId = getDiseaseId(disease);
			con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			statement = con.createStatement();

			String insertSymptom = "insert into symptom(symptomName) values";

			for (String symptom : symArray) {
				if (getSymptomId(symptom) == -1) {
					insertSymptom = "insert into symptom (symptomName) values('"
							+ symptom + "')";
					System.out.print("insert Query: " + insertSymptom);

					statement.execute(insertSymptom);
				}

			}
			for (String symptom : symArray) {
				int symptomId = getSymptomId(symptom);
				String insertDisSYm = "replace into dis_sym(idDisease,idSymptom) values("
						+ diseaseId + "," + symptomId + ")";
				System.out.print("insert Query: " + insertDisSYm);

				statement.execute(insertDisSYm);

			}
			statement.close();
			con.close();


		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public int deleteDisease(String diseaseName) {
		int diseaseID = getDiseaseId(diseaseName);
		
		String deleteDisFromDisSym = "Delete from dis_sym where idDisease = "+diseaseID;
		String deleteDisease = "Delete from disease where idDisease = " + diseaseID;
		System.out.println("deleteDisFromDisSym Query : "+deleteDisFromDisSym);
		System.out.println("deleteDisease Query : "+deleteDisease);
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();

			
			
			statement.execute(deleteDisFromDisSym);
			statement.execute(deleteDisease);
			statement.close();
			con.close();
			return 1;

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return -1;
		
	}
	
	public ArrayList<Symptom> getSymptoms(String diseaseName){
		ArrayList<Symptom> symptoms=new ArrayList<>();
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbName, dbUser, dbPassword);

			Statement statement = con.createStatement();
			String query = "select SymptomName from symptom s,dis_sym o,disease d" +
					" where s.idSymptom=o.idSymptom and d.idDisease=o.idDisease and " +
					"d.DiseaseName='"+diseaseName+"'";

			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);
			while (result.next()) {

				String symptom = result.getString("SymptomName");

				Symptom sym = new Symptom();
				sym.setSymptomName(symptom);
				symptoms.add(sym);
			}
			result.close();
			statement.close();
			con.close();

			
			
		}catch(Exception ex){
		
		}
		return symptoms;
	}
	

}